const router = require('koa-router')()
const fs = require("fs");
const path = require("path");
const uploadDir = path.join(__dirname, '../images/recordlist');
router.prefix('/recordlist')

//recordlist recordlistimg两个表合并
//页面初始化时获取数据库内的数据条目
router.post('/getrecordlistbackend', async (ctx, next) => {
    await ctx.db.EXCUTE("SET @@group_concat_max_len = 102400000000")
    let resultuserid = await ctx.db.EXCUTE("SELECT (SELECT username FROM user ni WHERE n.userid=ni.userid) AS userinfo FROM recordlist n")
    let resultcropid = await ctx.db.EXCUTE("SELECT (SELECT cropname FROM tags ni WHERE n.cropsid=ni.cropsid) AS cropinfo FROM recordlist n")
    let result1 = await ctx.db.EXCUTE("SELECT n.*, (SELECT GROUP_CONCAT(url SEPARATOR '><') FROM recordimg ni WHERE n.recordid=ni.recordid) AS comings FROM recordlist n")

    for (let i = 0; i < result1.length; i++) {
        result1[i].comings = (result1[i].comings || '').split('><');
        Object.assign(result1[i], resultuserid[i]);
        Object.assign(result1[i], resultcropid[i]);
    }

    ctx.body = {
        code: 200,
        data: result1
    }
})

//页面初始化时获取数据库内recordimg表的数据条目
router.post('/getrecordimgbackend', async (ctx, next) => {
    let sql0 = 'SELECT * FROM recordimg'
    let result = await ctx.db.EXCUTE(sql0);

    ctx.body = {
        code: 200,
        data: result
    };
})

// 根据id获取植物的生长日志
router.post('/getrecordlist', async (ctx, next) => {
    console.log(ctx.request.body);
    let id = ctx.request.body.id
    let sql = 'SELECT * FROM recordlist where id=? order by recorddate desc'
    let result = await ctx.db.EXCUTE(sql, [id]);
    console.log(result);
    for (let i = 0; i < result.length; i++) {
        let r = await ctx.db.EXCUTE("select * from recordimg where recordid=?", [result[i].recordid]);
        result[i].imgs = r
    }
    // console.log(result);
    // console.log(result[0].imgs);
    ctx.body = {
        code: 200,
        data: result
    };
})
// 删除一条生长日志
router.post('/delrecord', async (ctx, next) => {
    console.log(ctx.request.body);
    let recordid = ctx.request.body.recordid
    let sql = 'delete from recordlist where recordid=?'
    let result = await ctx.db.EXCUTE(sql, [recordid]);
    console.log(result);
    ctx.body = {
        code: 200,
        data: result
    };
})
// 上传作物生长记录
router.post('/upload', async (ctx, next) => {
    console.log(ctx.request.body);
    let cropsid = ctx.request.body.cropsid
    let userid = ctx.request.body.userid
    let recordtext = ctx.request.body.recordtext
    let recorddate = new Date().getTime()
    let img = ctx.request.body.img
    let recordid = new Date().getTime() + Math.round(Math.random() * (1000 - 1) + 1)
    let id = ctx.request.body.id

    let sql = "insert into recordlist (cropsid, userid, recordtext, recorddate, recordid, id) values (?, ?, ?, ?, ?, ?)"
    let result = await ctx.db.EXCUTE(sql, [cropsid, userid, recordtext, recorddate, recordid, id])
    console.log(img.length);
    for (let i in img) {
        // console.log(ctx.request.body.data.list.length);
        let base64 = img[i];
        let dataBuffer = Buffer.from(base64, 'base64');
        console.log(dataBuffer);
        let name = Date.now() + Math.floor(Math.random()) + '.jpg'
        let imgUrl = uploadDir+'\\'+name
        fs.writeFileSync(imgUrl, dataBuffer)
        let sql1 = "insert into recordimg (recordid, url) values (?, ?)"
        console.log(name);
        let result1 = await ctx.db.EXCUTE(sql1, [recordid, '/images/recordlist/'+name])
      }

    ctx.body = {
        code: 200,
        data: result
    }
})
module.exports = router